package ext.tianma.bom.expand.xls;


import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import wt.content.ApplicationData;
import wt.content.ContentHelper;
import wt.content.ContentHolder;
import wt.content.ContentItem;
import wt.content.ContentServerHelper;
import wt.content.FormatContentHolder;
import wt.doc.WTDocument;
import wt.session.SessionServerHelper;
import wt.util.WTException;
import ext.tianma.number.util.DocUtil;

/**
 * 类名: XlsReader </br>
 * 包名: ext.tianma.bom.expand.xls
 * 描述: 读取Xls公共抽象类，继承该类实现可自定义读取或通过XlsReaderDefaultImpl类实现默认操作
 * @author xucr 
 * 创建时间: 2020年6月1日
 * 发布版本: V1.0
 */
public abstract class XlsReader {
	private final static SimpleDateFormat FORMAR_DATE = new SimpleDateFormat("yyyy-MM-dd");
	private static final Logger logger = LoggerFactory.getLogger(XlsReader.class);
	
	private final static DecimalFormat FORMAT_DF = new DecimalFormat("0");

	/**
	 * 开始行
	 */
	private Integer startRow;

	/**
	 * 结束行
	 */
	private Integer endRow;

	/**
	 * 创建Workbook对象，实现XlsReader可访问
	 * 
	 * @author xucr
	 * @param xlsFileName
	 * @param localBoolean
	 *            true：读取本地文件,false:读取文档附件
	 * @throws Exception
	 */
	public abstract Workbook createWorkbook(String xlsFileName, Boolean localBoolean) throws Exception;

	/**
	 * 读取xls数据,实现XlsReader可访问
	 * @author xucr
	 * @param sheet
	 * @param clazz
	 * @return
	 */
	public abstract <T> Object readXls(Sheet sheet, Class<T> clazz) throws Exception;

	/**
	 * 根据OOTB文档获取WorkBook对象
	 * @author xucr
	 * @param doc 名称或编码
	 * @param flag true:根据编码获取文档对象,false:根据名称获取文档对象
	 * @return workbook
	 * @throws IOException
	 * @throws WTException
	 * @throws hw.apache.poi.openxml4j.exceptions.InvalidFormatException
	 */
	protected Workbook getWorkbookByWTDocument(String doc, boolean flag) throws IOException, WTException,
			InvalidFormatException
	{
		Workbook workbook = null;
		InputStream inputStream = null;
		boolean isAccessEnforced = SessionServerHelper.manager.isAccessEnforced();
		try
		{
			SessionServerHelper.manager.setAccessEnforced(false);
			WTDocument document = null;
			if (flag) {
				document = DocUtil.getWTDocumentByNumber(doc);
			} else {
				document = DocUtil.getWTDocumentByName(doc);
			}
			if (document != null)
			{
				ApplicationData appdata1 = getPrimaryContent(document);
				inputStream = ContentServerHelper.service.findContentStream(appdata1);
				if (inputStream != null)
				{
					workbook = WorkbookFactory.create(inputStream);
//					if(!inputStream.markSupported())
//						inputStream = new PushbackInputStream(inputStream, 8);
//		            if(POIFSFileSystem.hasPOIFSHeader(inputStream))//// 2003及以下
//		            	workbook = new HSSFWorkbook(inputStream);
//		            if(POIXMLDocument.hasOOXMLHeader(inputStream))// 2007及以上
//		            	workbook = new XSSFWorkbook(OPCPackage.open(inputStream));
				}
			}
		} finally
		{
			SessionServerHelper.manager.setAccessEnforced(isAccessEnforced);
			if (inputStream != null)
			{
				inputStream.close();
			}
		}
		return workbook;
	}
	
	public static ApplicationData getPrimaryContent(ContentHolder holder) throws WTException {
		ContentHolder contentHolder = null;
		ApplicationData applicationdata = null;
		try {
			contentHolder = ContentHelper.service.getContents(holder);
			logger.debug("ContentHolder : " + contentHolder);
			ContentItem contentitem = ContentHelper.getPrimary((FormatContentHolder) contentHolder);
			logger.debug("Primary : " + contentitem);
			applicationdata = (ApplicationData) contentitem;
		} catch (WTException e) {
			logger.error("getPrimaryContent():", e);
			throw new WTException(e, e.getLocalizedMessage());
		} catch (Exception e) {
			logger.error("getPrimaryContent():", e);
			throw new WTException(e, e.getLocalizedMessage());
		}
		return applicationdata;
	}

	/**
	 * 读取本地文件路径获取Workbook
	 * 
	 * @author xucr
	 * @param localFilePath
	 * @return workbook
	 * @throws Exception
	 */
	protected Workbook getWorkbookFromLocalFile(String localFilePath) throws Exception
	{
		Workbook workbook = null;
		FileInputStream fis = null;
		try
		{
			File file = new File(localFilePath);
			fis = new FileInputStream(file);
			workbook = WorkbookFactory.create(fis);
		} finally
		{
			if (fis != null)
			{
				fis.close();
			}
		}
		return workbook;
	}

	/**
	 * 获取Xls中单元格的值
	 * 
	 * @author xucr
	 * @param cell
	 * @return value
	 */
	protected String getCellValue(Cell cell) {
		String value = ""; // 默认是空字符串
		int cellType = cell.getCellType();
		switch (cellType)
		{
		case Cell.CELL_TYPE_BLANK:
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			value = String.valueOf(cell.getBooleanCellValue());
			break;
		case Cell.CELL_TYPE_ERROR:
			break;
		case Cell.CELL_TYPE_FORMULA:
			break;
		case Cell.CELL_TYPE_NUMERIC:
			double orginal = cell.getNumericCellValue();
			value = FORMAT_DF.format(orginal);
			if (HSSFDateUtil.isCellDateFormatted(cell))
			{
				Date date = DateUtil.getJavaDate(orginal);
				short format = cell.getCellStyle().getDataFormat();
				if (format != 20 && format != 32)
				{
					value = FORMAR_DATE.format(date);
				}
			} else
			{
				cell.setCellType(Cell.CELL_TYPE_STRING);
				value = cell.getStringCellValue();
			}
			break;
		case Cell.CELL_TYPE_STRING:
			value = cell.getStringCellValue();
			break;
		default:
			value = cell.getStringCellValue();
			break;
		}
		return ((value == null) ? null : value.trim());
	}

	/**
	 * 默认读取Xls数据函数,支持合并单元格
	 * @author xucr
	 * @param sheet
	 * @param clz
	 * @return listT
	 * @throws Exception
	 */
	protected <T> Object readData(Sheet sheet, Class<T> clz) throws Exception
	{
		List<T> listT = new ArrayList<T>();
		Map<String, Integer> fieldColIdxMapping = new HashMap<String, Integer>();
		List<String> rowNumbers = new ArrayList<String>();
		boolean isAnnotation = true;
		Field[] fields = clz.getDeclaredFields();
		for (Field field : fields)
		{
			String fieldName = field.getName();
			XlsColumn column = field.getAnnotation(XlsColumn.class);
			if (column != null)
			{
				isAnnotation = false;
				int colIdx = column.col();
				fieldColIdxMapping.put(fieldName, colIdx);
				if (column.rowNumber())
				{
					rowNumbers.add(fieldName);
				}
			}
		}
		if (isAnnotation)
		{
			throw new Exception(clz.getName() + " 中的字段不存在XlsColumn注解,无法解析Excel对应的列值匹配到对象属性中.");
		}
		int startRow = this.getStartRow();
		Iterator<Row> rows = sheet.iterator();
		while (rows.hasNext())
		{
			Row row = rows.next();
			int currentRowIdx = row.getRowNum();// 当前行
			if (currentRowIdx < startRow)
			{
				continue;
			}
			T objT = clz.newInstance();
			Iterator<Cell> cells = row.iterator();
			while (cells.hasNext())
			{
				Cell cell = cells.next();
				int currentCellIdx = cell.getColumnIndex();
				String cellValue = "";
				boolean isRegion = isMergedRegion(sheet,currentRowIdx,currentCellIdx);
				if(isRegion)
				{
					cellValue = getMergedRegionCellValue(sheet,currentRowIdx,currentCellIdx);
				}else
				{
					cellValue = getCellValue(cell);
				}
				for (Map.Entry<String, Integer> entry : fieldColIdxMapping.entrySet())
				{
					String fileName = entry.getKey();
					Integer colIdx = entry.getValue();
					if (currentCellIdx == colIdx)
					{
						Method writeMethod = getObjectMethod(clz, fileName, false);
						if (writeMethod != null)
						{
							writeMethod.invoke(objT, cellValue);
						}
					}
				}
			}
			for (String rowNumber : rowNumbers)
			{
				Method writeMethod = getObjectMethod(clz, rowNumber, false);
				if (writeMethod != null)
				{
					try
					{
						writeMethod.invoke(objT, currentRowIdx);
					} catch (IllegalArgumentException e)
					{
						e.printStackTrace();
						throw new Exception(clz.getName() + "中注解为rowNumber 的字段必须是Integer类型.");
					}
				}
			}
			listT.add(objT);
		}
		return listT;
	}
	
	/**
	 * 判断是否有合并行、列
	 * @author xucr
	 * @param sheet
	 * @param rowIdx
	 * @param colIdx
	 */
	protected boolean isMergedRegion(Sheet sheet,int rowIdx,int colIdx){
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++)
		{
			CellRangeAddress range = sheet.getMergedRegion(i);
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow =  range.getFirstRow();
			int lastRow = range.getLastRow();
			if(rowIdx>=firstRow && colIdx<= lastColumn){
				if(colIdx>=firstColumn && rowIdx<=lastRow){
					return true;
				}
			}
		}
		return false;
	}
	
	/**
	 * 获取合并单元格的值
	 * @author xucr
	 * @param sheet
	 * @param rowIdx
	 * @param colIdx
	 */
	protected String getMergedRegionCellValue(Sheet sheet,int rowIdx,int colIdx){
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++)
		{
			CellRangeAddress range = sheet.getMergedRegion(i);
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow =  range.getFirstRow();
			int lastRow = range.getLastRow();
			if(rowIdx>=firstRow && rowIdx<= lastRow){
				if(colIdx>=firstColumn && colIdx<=lastColumn){
					Row frow = sheet.getRow(firstRow);
					Cell fcell =frow.getCell(firstColumn);
					return getCellValue(fcell);
				}
			}
		}
		return "";
	}

	public Integer getStartRow()
	{
		return startRow;
	}

	public void setStartRow(Integer startRow)
	{
		this.startRow = startRow;
	}

	public Integer getEndRow()
	{
		return endRow;
	}

	public void setEndRow(Integer endRow)
	{
		this.endRow = endRow;
	}
	
	public static Method getObjectMethod(Class clz, String fieldName, boolean read) throws Exception {
		PropertyDescriptor pDescriptor = null;
		try
		{
			pDescriptor = new PropertyDescriptor(fieldName, clz);
		} catch (Exception e)
		{
			return null;
		}
		if (read)
		{
			return pDescriptor.getReadMethod();
		} else
		{
			return pDescriptor.getWriteMethod();
		}
	}
}
